package com.ctshk.rpc.tour.mapper.provider;

import org.apache.commons.lang3.StringUtils;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.ctshk.common.service.mapper.CtsSQL;
import com.ctshk.common.utils.Assist;
import com.ctshk.common.utils.BConst;
import com.ctshk.rpc.tour.dto.schedule.TripScheduleDTO;
import com.ctshk.rpc.tour.req.schedule.TripScheduleListQueryReq;

/**
 * @author Lenovo
 */
public class SeriesTripScheduleProvider {

	/**
     * 查询团期列表
     * @param page
     * @param tripScheduleListQueryReq
     * @param isBook 是否为预订列表查询
     * @return
     */
    public String scheduleList(IPage<TripScheduleDTO> page, TripScheduleListQueryReq tripScheduleListQueryReq, boolean isBook) {
        return new CtsSQL() {{
            SELECT("sts.id,sts.sort_key,sts.schedule_status,st.title,st.trip_code,sts.departure_date,st.travel_days,st.night_days,ststr.max_people,ststr.min_people,ststr.reserved_seats_num,sts.trip_number,sts.trip_id, sts.collection_place_json,sts.collection_place_json as collection_place_jsons,sts.dissolution_area_json, sts.dissolution_area_json as dissolution_area_jsons, sts.departure_consulation,sts.departure_time_type,sts.departure_time,sts.departure_traffic_type_id,sts.departure_traffic_type,sts.return_consulation,sts.return_time,sts.return_arrive_date_type,sts.return_arrive_time,sts.return_traffic_type_id,sts.return_traffic_type\n" +
                    ",ststr.has_tea_party,ststr.tea_party_loc_id,ststr.tea_party_date,ststr.tea_party_time,ststr.tea_party_remark,sts.cost_sharing_type,sts.locked_tourist_num,sts.back_date,sts.charge_person_id,sts.charge_person_name,sts.charge_person_number,sts.op_id,sts.op_name,sts.op_number,sts.self_sale_permission_type,sts.agent_sale_permission_type,\n" +
                    "st.departure_place_id,st.departure_place,st.destination_id,st.destination,sts.departure_consulation,sts.departure_time_type,sts.departure_time,sts.go_arrive_date_type,sts.go_arrive_time,sts.departure_traffic_type_id,sts.shop_summary,\n" +
                    "sts.departure_traffic_type,sts.return_consulation,sts.return_time,sts.return_arrive_date_type,sts.return_arrive_time,sts.return_traffic_type_id,sts.return_traffic_type,ststr.enable_additional_position,ststr.clear_seats_time,ststr.clear_seats_unit"
                    , "if(sts.schedule_status = 2, 2, if ((ifnull(sts.locked_tourist_num, 0) < ifnull(ststr.min_people, 0) and sts.departure_date <= DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)), 1, 3)) as scheduleStatusFlag");
            FROM("t_series_trip_schedule sts");
            
            LEFT_OUTER_JOIN("t_series_trip st ON ( sts.trip_id = st.id )");
            LEFT_OUTER_JOIN("t_series_trip_schedule_team_rule ststr ON (sts.id = ststr.schedule_id)");
            
            
            //线下自营权限条件
            if (Assist.isNotEmpty(tripScheduleListQueryReq.getOfflineSelfCustomPermissionIdList())) {
            	String itemSql = SQL_IN(tripScheduleListQueryReq.getOfflineSelfCustomPermissionIdList(), "tcosc.relation_id", "tripScheduleListQueryReq.offlineSelfCustomPermissionIdList");
            	WHERE("(sts.self_sale_permission_type = 2 or (sts.self_sale_permission_type = 3 and exists(select 1 from t_series_trip_schedule_channel_offline_self_custom tcosc where tcosc.is_deleted = 0 and tcosc.schedule_id = sts.id and " + itemSql + " limit 1)))");
            }
            
            //只查询有代理渠道
            if (tripScheduleListQueryReq.isQueryAgent()) {
            	WHERE("sts.agent_sale_permission_type in (2,3)");
            }
            
            IF_NOT_NULL_WHERE_IN(tripScheduleListQueryReq.getScheduleStatusList(), "sts.schedule_status", "tripScheduleListQueryReq.scheduleStatusList");
            
            //权限负责人或操作人
            if (Assist.isNotEmpty(tripScheduleListQueryReq.getUserList())) {
            	WHERE_AND_OR(SQL_IN(tripScheduleListQueryReq.getUserList(), "sts.charge_person_id", "tripScheduleListQueryReq.userList")
            			, SQL_IN(tripScheduleListQueryReq.getUserList(), "sts.op_id", "tripScheduleListQueryReq.userList"));
            }
            
            IF_NOT_NULL_WHERE_IN(tripScheduleListQueryReq.getScheduleIdList(), "sts.id", "tripScheduleListQueryReq.scheduleIdList");

            if (StringUtils.isNotBlank(tripScheduleListQueryReq.getTripNumber())) {
                WHERE("sts.trip_number like concat('%',#{tripScheduleListQueryReq.tripNumber},'%')");
            }
            if (null != tripScheduleListQueryReq.getScheduleStatus()) {
                WHERE("sts.schedule_status=#{tripScheduleListQueryReq.scheduleStatus}");
            }

            if (null != tripScheduleListQueryReq.getTravelDays()) {
                WHERE("sts.travel_days=#{tripScheduleListQueryReq.travelDays}");
            }

            if (null != tripScheduleListQueryReq.getGroupNatureId()) {
                WHERE("st.group_nature_id=#{tripScheduleListQueryReq.groupNatureId}");
            }
            if (null != tripScheduleListQueryReq.getGroupTypeId()) {
                WHERE("st.group_type_id=#{tripScheduleListQueryReq.groupTypeId}");
            }

            if (null != tripScheduleListQueryReq.getTourThemeId()) {
//                WHERE("st.tour_theme_json=#{tripScheduleListQueryReq.tourThemeId}");
//            	WHERE("JSON_CONTAINS(st.tour_theme_json -> '$[*].id', '#{tripScheduleListQueryReq.tourThemeId}', '$')");
            	WHERE("st.tour_theme_json like concat('%',#{tripScheduleListQueryReq.tourThemeId},'%')");
            }

            if (null != tripScheduleListQueryReq.getTourTypeId()) {
                WHERE("st.tour_type_id=#{tripScheduleListQueryReq.tourTypeId}");
            }

            if (null != tripScheduleListQueryReq.getTripId()) {
                WHERE("sts.trip_id = #{tripScheduleListQueryReq.tripId}");
            }
            if (null != tripScheduleListQueryReq.getTripTitle()) {
            	WHERE("st.title like concat('%',#{tripScheduleListQueryReq.tripTitle},'%')");
            }
            if (null != tripScheduleListQueryReq.getDepartureBeginDate() || tripScheduleListQueryReq.getDepartureEndDate() != null) {
            	if (tripScheduleListQueryReq.getDepartureBeginDate() != null) {
            		WHERE("sts.departure_date >= #{tripScheduleListQueryReq.departureBeginDate}");
            	}
            	if (tripScheduleListQueryReq.getDepartureEndDate() != null) {
            		WHERE("sts.departure_date < #{tripScheduleListQueryReq.departureEndDate}");
            	}
            }
//            if (null != tripScheduleListQueryReq.getDepartureBeginDate() && tripScheduleListQueryReq.getDepartureEndDate() != null) {
//                WHERE("sts.departure_date BETWEEN #{tripScheduleListQueryReq.departureBeginDate} AND #{tripScheduleListQueryReq.departureEndDate}");
//            }

            if (null != tripScheduleListQueryReq.getBackBeginDate() && null != tripScheduleListQueryReq.getBackEndDate()) {
                WHERE("sts.back_date BETWEEN #{tripScheduleListQueryReq.backBeginDate} AND #{tripScheduleListQueryReq.backEndDate}");
            }
            if (null != tripScheduleListQueryReq.getTripCode()) {
                WHERE("st.trip_code like concat('%',#{tripScheduleListQueryReq.tripCode},'%')");
            }
            if (null != tripScheduleListQueryReq.getChargePersonId()) {
                WHERE("sts.charge_person_id  = #{tripScheduleListQueryReq.chargePersonId}");
            }
            if (null != tripScheduleListQueryReq.getOpId()) {
                WHERE("sts.op_id  = #{tripScheduleListQueryReq.opId}");
            }
            
            if (CollectionUtils.isNotEmpty(tripScheduleListQueryReq.getTypeList())) {
            	//急收团
            	if (tripScheduleListQueryReq.getTypeList().contains(1)) {
            		WHERE("(ifnull(sts.locked_tourist_num, 0) < ifnull(ststr.min_people, 0) and sts.departure_date <= DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))");
            	}
            	//已成团
            	if (tripScheduleListQueryReq.getTypeList().contains(2)) {
            		WHERE("sts.schedule_status = 2");
            	}
            	//可收客
            	if (tripScheduleListQueryReq.getTypeList().contains(3)) {
            		WHERE("(ifnull(ststr.max_people, 0) - ifnull(sts.locked_tourist_num, 0)) > 0");
            	}
            	//已收客
            	if (tripScheduleListQueryReq.getTypeList().contains(4)) {
            		WHERE("ifnull(sts.locked_tourist_num, 0) > 0");
            	}
            }

            //周几
            IF_NOT_NULL_WHERE_IN(tripScheduleListQueryReq.getDepartureDayOfWeek(), "date_format(sts.departure_date,'%w')", "tripScheduleListQueryReq.departureDayOfWeek");

            if (tripScheduleListQueryReq.getPriceBegin() != null || tripScheduleListQueryReq.getPriceEnd() != null) {
                String priceBeginSql = tripScheduleListQueryReq.getPriceBegin() != null ? " and tsdc.group_customer_price >= #{tripScheduleListQueryReq.priceBegin} " : BConst.EMPTY;
                String priceEndSql = tripScheduleListQueryReq.getPriceEnd() != null ? " and tsdc.group_customer_price <= #{tripScheduleListQueryReq.priceEnd}  " : BConst.EMPTY;
                WHERE("exists (select 1 from t_series_trip_schedule_default_cost tsdc where tsdc.is_deleted = 0 and tsdc.schedule_id = sts.id and tsdc.is_deleted = 0 " + priceBeginSql + " " + priceEndSql + " limit 1)");
            }
            
            if (Assist.isNotBlank(tripScheduleListQueryReq.getTripContent())) {
            	WHERE("(sts.trip_number like concat('%', #{tripScheduleListQueryReq.tripContent}, '%') or st.trip_code like concat('%', #{tripScheduleListQueryReq.tripContent}, '%') or st.title like concat('%', #{tripScheduleListQueryReq.tripContent}, '%'))");
            }
            
            IF_NOT_NULL_WHERE_IN(tripScheduleListQueryReq.getChargePersonIdList(), "sts.charge_person_id", "tripScheduleListQueryReq.chargePersonIdList");
            if (tripScheduleListQueryReq.getDeparturePlaceId() != null) {
            	WHERE("st.departure_place_id = #{tripScheduleListQueryReq.departurePlaceId}");
            }
            if (tripScheduleListQueryReq.getDestinationId() != null) {
            	WHERE("st.destination_id = #{tripScheduleListQueryReq.destinationId}");
            }
            
            //预订列表查询
            if (isBook) {
            	ORDER_BY("scheduleStatusFlag asc, departure_date desc");
            } else {  //正常查询
            	ORDER_BY("sts.gmt_create desc");
            }

        }}.toString();
    }
}
